create or replace package EquipmentData is
  -- Created : 12/26/2008 9:49:28 AM
  -- Purpose : to support EquipmentData function in InSite

  -- Public type declarations
  --type <TypeName> is <Datatype>;

  -- Public constant declarations
  --<ConstantName> constant <Datatype> := <Value>;

  -- Public variable declarations
  --<VariableName> <Datatype>;

  -- Public function and procedure declarations
  function GetPendingEquipmentDataSetup( inSpecID IN spec.specid%TYPE,
                                         inEquipmentID IN resourcedef.resourceid%TYPE,
                                         inTriggerType IN a_wipdatasetup.triggertype%TYPE,
                                         inContainerID IN container.containerid%TYPE,
                                         inProductID IN product.productid%TYPE
  ) return VARCHAR2;

  -- Helper function to debug above function
  function Helper(inContainerName IN container.containername%TYPE,
                  inEquipmentName in Resourcedef.Resourcename%TYPE,
                  inTriggerType   IN number)
  return varchar2;

end EquipmentData;
/
create or replace package body EquipmentData is

  -- Private type declarations
  --type <TypeName> is <Datatype>;

  -- Private constant declarations
  --<ConstantName> constant <Datatype> := <Value>;

  -- Private variable declarations
  --<VariableName> <Datatype>;

  -- Function and procedure implementations

  -- private function to find needed EquipmentDataSetup name in Device Change trigger condition
  PROCEDURE GetLastDCDateTimeForProduct(inEquipmentID IN resourcedef.resourceid%TYPE,
                                        inProductID   product.productid%TYPE,
                                        inLastDateTime  IN DATE,
                                        outLastDateTime OUT DATE,
                                        outNextProcessProduct OUT product.productid%TYPE
  ) IS
    vLastProductExistCount NUMBER(10) := 0;
    vLastDate              DATE;
    vNextProcessProduct    product.productid%TYPE;
    cursor curGetLastTime is
      SELECT a.laststatuschangedate, a.productid
      FROM productionstatus a WHERE a.resourceid = inEquipmentID;
  BEGIN
    outLastDateTime := inLastDateTime;
    SELECT COUNT(*) INTO vLastProductExistCount
    FROM productionstatus ecs
    WHERE ecs.resourceid = inEquipmentID AND ecs.productid = inProductID;
    IF vLastProductExistCount = 0 THEN  -- Device change happend
       open curGetLastTime;
       fetch curGetLastTime into vLastDate, vNextProcessProduct;
       if curGetLastTime%Found then
          outLastDateTime := vLastDate;
          outNextProcessProduct := vNextProcessProduct;
       end if;
       close curGetLastTime;
    END IF;
  END GetLastDCDateTimeForProduct;


    PROCEDURE GetLastDCDateTimeForSpec(inEquipmentID IN resourcedef.resourceid%TYPE,
                                       inSpecID   spec.specid%TYPE,
                                       inLastDateTime  IN DATE,
                                       outLastDateTime OUT DATE,
                                       outNextProcessSpec OUT spec.specid%TYPE
  ) IS
    vLastSpecExistCount NUMBER(10) := 0;
    vLastDate              DATE;
    vNextProcessSpec       spec.specid%TYPE;
    cursor curGetLastTime is
      SELECT a.laststatuschangedate, a.productid
      FROM productionstatus a WHERE a.resourceid = inEquipmentID;
  BEGIN
    outLastDateTime := inLastDateTime;
    SELECT COUNT(*) INTO vLastSpecExistCount
    FROM productionstatus ecs
    WHERE ecs.resourceid = inEquipmentID AND ecs.lastspecid = inSpecID;
    IF vLastSpecExistCount = 0 THEN  -- Device change happend
       open curGetLastTime;
       fetch curGetLastTime into vLastDate, vNextProcessSpec;
       if curGetLastTime%Found then
          outLastDateTime := vLastDate;
          outNextProcessSpec := vNextProcessSpec;
       end if;
       close curGetLastTime;
    END IF;
  END GetLastDCDateTimeForSpec;

  -- private function to find needed EquipmentDataSetup name in Shift Change trigger condition
  PROCEDURE GetLastShiftChangeDateTime(outLastDateTime OUT DATE )
  IS
    TIMEA          constant varchar2(8) := '08:30:00';
    TIMEB          constant varchar2(8) := '20:30:00';
    vCurrentDate   varchar2(10);
    vCurrentDateA  Date;
    vCurrentDateB  Date;
    vSysdate       Date;
  BEGIN
    vSysdate := sysdate;
    vCurrentDate := to_char(vSysdate,'YYYY-MM-DD');
    vCurrentDateA := to_date(vCurrentDate || ' ' || TIMEA,'YYYY-MM-DD HH24:MI:SS');
    vCurrentDateB := to_date(vCurrentDate || ' ' || TIMEB,'YYYY-MM-DD HH24:MI:SS');
    if vSysdate < vCurrentDateA then
       outLastDateTime := vCurrentDateB - 1;
    elsif vSysdate >= vCurrentDateA and vSysdate < vCurrentDateB then
       outLastDateTime := vCurrentDateA;
    else
       outLastDateTime := vCurrentDateB;
    end if;
  END GetLastShiftChangeDateTime;

  -- private function to find needed EquipmentDataSetup name in Equipment Repair trigger condition
  PROCEDURE GetLastEquipmentRepairDateTime( inEquipmentID IN resourcedef.resourceid%TYPE,
                                            outLastDateTime OUT DATE
  ) IS
  BEGIN
    SELECT hml.txndate INTO outLastDateTime
    FROM Resourcestatushistory rsh
       INNER JOIN HISTORYMAINLINE hml ON hml.historymainlineid = rsh.historymainlineid
    WHERE rsh.OLDSTATUSNAME = 'BUYOFF' AND rsh.NEWSTATUSNAME = 'IDLE'
      AND hml.resourceid = inEquipmentID
      AND hml.TXNDATE >= SYSDATE - 7       -- reduce scanning range
    ORDER BY hml.txndate DESC;
  exception
      when no_data_found then
          outLastDateTime := SYSDATE - 365;      -- By pass 1 year
  END GetLastEquipmentRepairDateTime;

  -- Find match EquipmentDataSetup in provide condition
  PROCEDURE FindEDSetupName(inSpecID IN spec.specid%TYPE,
                            inProductID product.productid%TYPE,
                            inTriggerType IN a_wipdatasetup.triggertype%TYPE,
                            outEDSetupID OUT a_wipdatasetup.wipdatasetupid%TYPE,
                            outTolerantHour OUT a_wipdatasetup.toleranthour%TYPE
  ) IS
      cursor cur_GetEDSetupName 
      (
      curSpec IN spec.specid%TYPE,
      curTriggerType IN a_wipdatasetup.triggertype%TYPE,
      curProductID IN product.productid%TYPE
      ) is
      SELECT 
      A.Wipdatasetupid EQUIPMENTDATASETUPID,
      A.TOLERANTHOUR 
      FROM 
      A_WIPDataSetup A,
      A_EqpDataDeviceGroup B,
      a_Eqpdatadevicegroupdetails C
      WHERE A.SPECID = curSpec 
      AND A.TRIGGERTYPE = curTriggerType
      AND A.Equipmentdatadevicegroupid = B.Eqpdatadevicegroupid(+) 
      AND B.Eqpdatadevicegroupid = C.Eqpdatadevicegroupid(+)
      AND (C.Productid = curProductID OR C.Productid IS NULL)
      ORDER BY C.PRODUCTID ASC;
  BEGIN
     outEDSetupID := '';
     outTolerantHour := '';
     open cur_GetEDSetupName(inSpecId,inTriggerType,inProductID);
     fetch cur_GetEDSetupName into outEDSetupID,outTolerantHour;
     close cur_GetEDSetupName;
  END;

  --
  PROCEDURE FindEquipmentDataLastDateTime( inEquipmentID IN resourcedef.resourceid%TYPE,
                                           inEDSetupID IN a_wipdatasetup.wipdatasetupid%TYPE,
                                           outLastDateTime OUT DATE,
                                           outNeedQAConfirm OUT a_equipmentdata.needqaconfirm%TYPE
  ) IS
     cursor cur_GetLastTime (curEDSetupID IN a_wipdatasetup.wipdatasetupid%TYPE,curEquipmentID IN resourcedef.resourceid%TYPE) is
     SELECT 
     ar.creationtimestamp TXNTIMESTAMP,
     A.Needqaconfirm 
     FROM 
     A_EQUIPMENTDATA A,
     a_adhocwipdatarecord ar     
     WHERE
      a.adhocwipdatarecordid=ar.adhocwipdatarecordid 
      and A.WIPDATASETUPID = curEDSetupID 
      AND A.Parentid = curEquipmentID;
  BEGIN
     outLastDateTime := '';
     open cur_GetLastTime(inEDSetupID,inEquipmentID);
     fetch cur_GetLastTime into outLastDateTime,outNeedQAConfirm;
     close cur_GetLastTime;
  END;


   PROCEDURE GetLotDataCollection( inEquipmentID IN resourcedef.resourceid%TYPE,
                                   inContainerID IN container.containerid%TYPE,
                                   inProductID IN  product.productid%TYPE,
                                   inSpecID IN spec.specid%TYPE,
                                   outFindData out number
   ) IS
       vCount                      number;
   BEGIN
       outFindData := 1;
       if inContainerID is not null and inProductID is not null then
           SELECT COUNT(*) INTO vCount FROM A_WIPDataSetup A WHERE A.Specid = inSpecID;
           if vCount > 0 then
             SELECT COUNT(*) INTO outFindData FROM a_equipmentdata A
             WHERE A.Containerid = inContainerID AND A.Parentid = inEquipmentID;
           end if;
       end if;
   END;


  -- called by InSite, do not change declare
  function GetPendingEquipmentDataSetup( inSpecID IN spec.specid%TYPE,
                                         inEquipmentID IN resourcedef.resourceid%TYPE,
                                         inTriggerType IN a_wipdatasetup.triggertype%TYPE,
                                         inContainerID IN container.containerid%TYPE,
                                         inProductID IN  product.productid%TYPE
  ) return VARCHAR2 is
    vEDSetupName            a_wipdatasetup.wipdatasetupname%TYPE;
    vIsAlert                VARCHAR2(10);
    vFindLotCollectionData  number;
    vMessage                varchar2(100);
    vReturn                 varchar2(200);

    vTitleMsg               varchar2(20);
    NEEDINPUT               constant varchar2(20) := ' data needs input.';
    NEEDQACONFIRM           constant varchar2(25) := ' data needs QA confirm.';
    NEEDSETPRODUCT          constant varchar2(39) := ' need set correct next process device';
    NEEDSETSPEC             constant varchar2(35) := ' need set correct next process spec';
    LOTCOLLECTION           constant varchar2(14) := 'Lot Collection';

    SHIFTCHANGE             constant char(1) := '1';
    DEVICECHANGE            constant char(1) := '2';
    EQUIPMENTREPAIR         constant char(1) := '3';
    vEDSetupID              a_wipdatasetup.wipdatasetupid%TYPE;
    vTolerantHour           a_wipdatasetup.toleranthour%TYPE;
    vNeedQAConfirm          a_equipmentdata.needqaconfirm%TYPE;
    vEquipmentdataLastTime  DATE;
    vLastTime               DATE;
    vNextProcessProduct     product.productid%TYPE;
    vNextProcessSpec        spec.specid%TYPE;
  BEGIN
    vReturn := '^ ^ '; -- '^^' is require for InSite depend '^' to split EDSetupName and Alert
    vFindLotCollectionData := -1;

    -- Trigger Type
    -- -1: All (used in query in TrackInLot only)
    --  0: None
    --  1: ShiftChange
    --  2: DeviceChange
    --  3: EquipmentRepair

    if inTriggerType = -1 or inTriggerType = 2 then
        --Check Device Change
        vTitleMsg := 'Device Change';
        FindEDSetupName(inSpecID,inProductID,DEVICECHANGE,vEDSetupID,vTolerantHour);
        if Trim(vEDSetupID) is not null then
           if vFindLotCollectionData = -1 then
              GetLotDataCollection(inEquipmentID,inContainerID,inProductID,inSpecID,vFindLotCollectionData);  -- Check Lot Collection Data
           end if;
           if vFindLotCollectionData > 0 then
               FindEquipmentDataLastDateTime(inEquipmentID,vEDSetupID,vEquipmentdataLastTime,vNeedQAConfirm);
               if vEquipmentdataLastTime is not null then
                  if vNeedQAConfirm = '0' then
                      GetLastDCDateTimeForProduct(inEquipmentID,inProductID,vEquipmentdataLastTime,vLastTime,vNextProcessProduct);
                      if vLastTime > vEquipmentdataLastTime then
                         vIsAlert := 'HOLD'; -- Device Change is not allow Alert for there is no txndate available in MES
                         vMessage := vTitleMsg || NEEDINPUT;
                      ELSE
                         IF vNextProcessProduct = inProductID OR vNextProcessProduct IS NULL THEN
                             vIsAlert := 'DONE';
                         ELSE
                             vIsAlert := 'HOLD';
                             vMessage := vTitleMsg || NEEDSETPRODUCT;
                         END IF;
                      end if;
                      if vIsAlert <> 'HOLD' then  -- To Check Spec change on Device
                         GetLastDCDateTimeForSpec(inEquipmentID,inSpecID,vEquipmentdataLastTime,vLastTime,vNextProcessSpec);
                         if vLastTime > vEquipmentdataLastTime then
                           vIsAlert := 'HOLD'; -- Device Change is not allow Alert for there is no txndate available in MES
                           vMessage := vTitleMsg || NEEDINPUT;
                        ELSE
                           IF vNextProcessSpec = inSpecID OR vNextProcessSpec IS NULL THEN
                               vIsAlert := 'DONE';
                           ELSE
                               vIsAlert := 'HOLD';
                               vMessage := vTitleMsg || NEEDSETSPEC;
                           END IF;
                        end if;
                      end if;
                  else
                      vIsAlert := 'HOLD';  -- Need QA Confirm
                      vMessage := vTitleMsg || NEEDQACONFIRM;
                  end if;
               else
                  vIsAlert := 'HOLD';
                  vMessage := vTitleMsg || NEEDINPUT;
               end if;
           else
               vIsAlert := 'HOLD';  -- No Lot Collection Data
               vMessage := LOTCOLLECTION || NEEDINPUT;
           end if;
        end if;
    end if;


    if inTriggerType = -1 or inTriggerType = 3 then
        if vIsAlert is null or vIsAlert = 'DONE' then
        --Check Equipment Repair
          vTitleMsg := 'Equipment Repair';
          FindEDSetupName(inSpecID,inProductID,EQUIPMENTREPAIR,vEDSetupID,vTolerantHour);
          if Trim(vEDSetupID) is not null then
             if vFindLotCollectionData = -1 then
                GetLotDataCollection(inEquipmentID,inContainerID,inProductID,inSpecID,vFindLotCollectionData);  -- Check Lot Collection Data
             end if;
             if vFindLotCollectionData > 0 then
                 FindEquipmentDataLastDateTime(inEquipmentID,vEDSetupID,vEquipmentdataLastTime,vNeedQAConfirm);
                 if vEquipmentdataLastTime is not null then
                    if vNeedQAConfirm = '0' then
                        GetLastEquipmentRepairDateTime(inEquipmentID,vLastTime);
                        if vLastTime > vEquipmentdataLastTime then
                           if to_number(sysdate - vLastTime) * 24 >= vTolerantHour then
                              vIsAlert := 'HOLD';
                           else
                              vIsAlert := 'ALERT';
                           end if;
                           vMessage := vTitleMsg || NEEDINPUT;
                        else
                           vIsAlert := 'DONE';
                        end if;
                    else
                        vIsAlert := 'HOLD';  -- Need QA Confirm
                        vMessage := vTitleMsg || NEEDQACONFIRM;
                    end if;
                 else
                    vIsAlert := 'HOLD';
                    vMessage := vTitleMsg || NEEDINPUT;
                 end if;
             else
                 vIsAlert := 'HOLD';         -- No Lot Collection Data
                 vMessage := LOTCOLLECTION || NEEDINPUT;
             end if;
          end if;
        end if;
    end if;


    if inTriggerType = -1 or inTriggerType = 1 then
        if vIsAlert is null or vIsAlert = 'DONE' then
         -- Check Shift Change
          vTitleMsg := 'Shift Change';
          FindEDSetupName(inSpecID,inProductID,SHIFTCHANGE,vEDSetupID,vTolerantHour);
          if Trim(vEDSetupID) is not null then
             if vFindLotCollectionData = -1 then
                GetLotDataCollection(inEquipmentID,inContainerID,inProductID,inSpecID,vFindLotCollectionData);  -- Check Lot Collection Data
             end if;
             if vFindLotCollectionData > 0 then
                 FindEquipmentDataLastDateTime(inEquipmentID,vEDSetupID,vEquipmentdataLastTime,vNeedQAConfirm);
                 if vEquipmentdataLastTime is not null then
                    if vNeedQAConfirm = '0' then
                        GetLastShiftChangeDateTime(vLastTime);
                        if vLastTime > vEquipmentdataLastTime then
                           if to_number(sysdate - vLastTime) * 24 >= vTolerantHour then
                              vIsAlert := 'HOLD';
                           else
                              vIsAlert := 'ALERT';
                           end if;
                           vMessage := vTitleMsg || NEEDINPUT;
                        else
                           vIsAlert := 'DONE'; -- Means there is EDSetup need to fill, but it is already filled, so is DONE
                        end if;
                    else
                        vIsAlert := 'HOLD';  -- Need QA Confirm
                        vMessage := vTitleMsg || NEEDQACONFIRM;
                    end if;
                 else
                    vIsAlert := 'HOLD';
                    vMessage := vTitleMsg || NEEDINPUT;
                 end if;
             else
                 vIsAlert := 'HOLD';    -- No Lot Collection Data found
                 vMessage := LOTCOLLECTION || NEEDINPUT;
             end if;
          end if;
        end if;
    end if;

    if inTriggerType = -1 then
        if vIsAlert is null or vIsAlert = 'DONE' THEN
           -- Check DA Equipment Data Input
           vTitleMsg := 'DA Equipment';
        end if;
    end if;

    if vIsAlert is not null then
       if vIsAlert <> 'DONE' then
           SELECT A.Wipdatasetupname EQUIPMENTDATASETUPNAME INTO vEDSetupName FROM A_WIPDataSetup A WHERE A.Wipdatasetupid = vEDSetupID;
            -- vIsAlert can be 'ALERT' or 'HOLD'
           vReturn := vEDSetupName || '^' || vIsAlert || '^' || vMessage;
       else
           vReturn := '^' || vIsAlert || '^ ';
       end if;
    end if;

    return(vReturn);
  END GetPendingEquipmentDataSetup;


  function Helper(inContainerName IN container.containername%TYPE,
                  inEquipmentName IN Resourcedef.Resourcename%TYPE,
                  inTriggerType   IN number)
  return varchar2
  is
           vSpecID               currentstatus.specid%TYPE;
           vContainerID          container.containerid%TYPE;
           vProductID            container.productid%TYPE;
           vEquipmentID          resourcedef.resourceid%TYPE;
           vReturn               VARCHAR2(100);
  begin

      SELECT A.CONTAINERID,B.SPECID,A.PRODUCTID INTO vContainerID,vSpecID,vProductID FROM CONTAINER A,CURRENTSTATUS B WHERE A.CURRENTSTATUSID = B.CURRENTSTATUSID AND A.CONTAINERNAME = inContainerName;
      SELECT C.RESOURCEID INTO vEquipmentID FROM RESOURCEDEF C WHERE C.RESOURCENAME = inEquipmentName;
      vReturn := GetPendingEquipmentDataSetup(vSpecID,vEquipmentID,inTriggerType,vContainerID,vProductID);
      return vReturn;
  end Helper;

end EquipmentData;
/
